using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Data.SqlClient;
// using UDS.Server.Management.Data.Sql;
using UDS.Build.Database.Replication;

namespace UDS.Build.Database
{
    class SprocReplicationGenerator
    {
        private const string ReplicationSprocSchemaName = "dbo";

        private PublishedTableDefinition _PublishedTable;
        private TextWriter w;
        private string whereClause;

        #region Constructor
        public SprocReplicationGenerator(PublishedTableDefinition publishedTable, TextWriter writer)
        {
            _PublishedTable = publishedTable;
            w = writer;
            if (_PublishedTable.Columns != null)
            {
                whereClause = GetUpdateWhereClause();
            }
        }

        #endregion 

        #region Common Generator SQL statement

        private SqlDbType GetSqlDbTypeFromSQLDataTypes(SQLDataTypes type)
        {
            switch (type)
            {
                case SQLDataTypes.bigint:
                    return SqlDbType.BigInt;
                case SQLDataTypes.binary:
                    return SqlDbType.Binary;
                case SQLDataTypes.bit:
                    return SqlDbType.Bit;
                case SQLDataTypes.@char:
                    return SqlDbType.Char;
                case SQLDataTypes.datetime:
                    return SqlDbType.DateTime;
                case SQLDataTypes.@decimal:
                    return SqlDbType.Decimal;
                case SQLDataTypes.@float:
                    return SqlDbType.Float;
                case SQLDataTypes.image:
                    return SqlDbType.Image;
                case SQLDataTypes.@int:
                    return SqlDbType.Int;
                case SQLDataTypes.money:
                    return SqlDbType.Money;
                case SQLDataTypes.nchar:
                    return SqlDbType.NChar;
                case SQLDataTypes.ntext:
                    return SqlDbType.NText;
                case SQLDataTypes.numeric:
                    return SqlDbType.Decimal;
                case SQLDataTypes.nvarchar:
                    return SqlDbType.NVarChar;
                case SQLDataTypes.real:
                    return SqlDbType.Real;
                case SQLDataTypes.smalldatetime:
                    return SqlDbType.SmallDateTime;
                case SQLDataTypes.smallint:
                    return SqlDbType.SmallInt;
                case SQLDataTypes.smallmoney:
                    return SqlDbType.SmallMoney;
                case SQLDataTypes.sql_variant:
                    return SqlDbType.Variant;
                case SQLDataTypes.text:
                    return SqlDbType.Text;
                case SQLDataTypes.timestamp:
                    return SqlDbType.Timestamp;
                case SQLDataTypes.tinyint:
                    return SqlDbType.TinyInt;
                case SQLDataTypes.uniqueidentifier:
                    return SqlDbType.UniqueIdentifier;
                case SQLDataTypes.varbinary:
                    return SqlDbType.VarBinary;
                case SQLDataTypes.varchar:
                    return SqlDbType.VarChar;
                case SQLDataTypes.xml:
                    return SqlDbType.Xml;
                default:
                    throw new ArgumentException("Invalid data type.");
            }
        }

        private SqlParameter[] GetColumnParameters(PublicationPublishedTableArticleColumn[] columns, string variablePrefix)
        {
            SqlParameter[] parameters = new SqlParameter[columns.Length];
            for (int i = 0; i < columns.Length; i++)
            {
                PublicationPublishedTableArticleColumn col = columns[i];
                parameters[i] = new SqlParameter(string.Format("@{0}{1}", variablePrefix, i + 1), GetSqlDbTypeFromSQLDataTypes(col.SourceDataType));
                if (col.SourceDataWidthSpecified)
                {
                    parameters[i].Size = col.SourceDataWidth;
                }
            }
            return parameters;
        }

        #endregion

        #region Insert statement

        public void GetInsertSQL()
        {
            SqlWriter sqlW = new SqlWriter(w);
            string procName = "sp_MSins_" + _PublishedTable.Name;
            sqlW.WriteSprocDrop(ReplicationSprocSchemaName, procName);
            if (_PublishedTable.Columns != null)
            {
                sqlW.WriteSprocCreate(ReplicationSprocSchemaName, procName, GetColumnParameters(_PublishedTable.Columns, "c"));
                // Write actual insert code
                sqlW.WriteText("insert into [");
                sqlW.WriteText(_PublishedTable.Name);
                sqlW.WriteText("]( \r\n");
                for (int i = 1; i <= _PublishedTable.Columns.Length; i++)
                {
                    PublicationPublishedTableArticleColumn col = _PublishedTable.Columns[i - 1];
                    sqlW.WriteText("[" + col.ColumnName + "]");
                    if (i != _PublishedTable.Columns.Length)
                    {
                        sqlW.WriteText(", ");
                    }
                }
                sqlW.WriteText("\r\n )\r\n\r\nvalues ( \r\n");
                for (int i = 1; i <= _PublishedTable.Columns.Length; i++)
                {
                    PublicationPublishedTableArticleColumn col = _PublishedTable.Columns[i - 1];
                    sqlW.WriteText("@c" + i.ToString());
                    if (i != _PublishedTable.Columns.Length)
                    {
                        sqlW.WriteText(", ");
                    }
                }
                sqlW.WriteText("\r\n )\r\n\r\n");

                sqlW.WriteSprocEpilog();
            }
        }

        #endregion

        #region Delete statement
        public void GetDeleteSQL()
        {
            SqlWriter sqlW = new SqlWriter(w);
            string procName = "sp_MSdel_" + _PublishedTable.Name;
            sqlW.WriteSprocDrop(ReplicationSprocSchemaName, procName);
            if (_PublishedTable.Columns != null)
            {
                sqlW.WriteSprocCreate(ReplicationSprocSchemaName, procName, GetColumnParameters(_PublishedTable.PKColumns, "pkc"));

                // Write delete sproc 
                sqlW.WriteText("delete [");
                sqlW.WriteText(_PublishedTable.Name);
                sqlW.WriteText("]\r\nwhere ");
                sqlW.WriteText(whereClause);
                sqlW.WriteText("");
                sqlW.WriteText("\r\nif @@rowcount = 0\r\n\tif @@microsoftversion>0x07320000\r\n\t\texec sp_MSreplraiserror 20598");
                sqlW.WriteSprocEpilog();
            }
        }

        #endregion

        #region Update statement
        public void GetUpdateSQL()
        {
            SqlWriter sqlW = new SqlWriter(w);
            string procName = "sp_MSupd_" + _PublishedTable.Name;
            sqlW.WriteSprocDrop(ReplicationSprocSchemaName, procName);
            if (_PublishedTable.Columns != null)
            {
                SqlParameter[] pkParams = GetColumnParameters(_PublishedTable.PKColumns, "pkc");
                SqlParameter[] colParams = GetColumnParameters(_PublishedTable.Columns, "c");
                SqlParameter[] procParams = new SqlParameter[pkParams.Length + colParams.Length + 1];
                Array.Copy(colParams, procParams, colParams.Length);
                Array.Copy(pkParams, 0, procParams, colParams.Length, pkParams.Length);
                //Set last param to bitmap param
                SqlParameter bitmapParam = new SqlParameter("@bitmap", SqlDbType.Binary);
                bitmapParam.Size = Convert.ToInt32((_PublishedTable.Columns.Length / 8) + 1);
                procParams[procParams.Length - 1] = bitmapParam;

                sqlW.WriteSprocCreate(ReplicationSprocSchemaName, procName, procParams);

                if (_PublishedTable.PKColumns.Length != _PublishedTable.Columns.Length)
                {
                    sqlW.WriteText(GetPKIfStatment());
                    sqlW.WriteText("\r\nBEGIN\r\n");
                }
                sqlW.WriteText("\r\nupdate [");
                sqlW.WriteText(_PublishedTable.Name);
                sqlW.WriteText("] set\r\n");
                sqlW.WriteText(GetUpdateColumns(false));
                sqlW.WriteText("\r\nwhere ");
                sqlW.WriteText(whereClause);
                sqlW.WriteText("\r\nif @@rowcount = 0\r\n\tif @@microsoftversion>0x07320000\r\n\t\texec sp_MSreplraiserror 20598");

                if (_PublishedTable.PKColumns.Length != _PublishedTable.Columns.Length)
                {
                    sqlW.WriteText("\r\nEND\r\nELSE\r\nBEGIN\r\nupdate [");
                    sqlW.WriteText(_PublishedTable.Name);
                    sqlW.WriteText("] set\r\n");
                    sqlW.WriteText(GetUpdateColumns(true));
                    sqlW.WriteText("\r\nwhere ");
                    sqlW.WriteText(whereClause);
                    sqlW.WriteText("\r\nif @@rowcount = 0\r\n\tif @@microsoftversion>0x07320000\r\n\t\texec sp_MSreplraiserror 20598");
                    sqlW.WriteText("\r\nEND");
                }

                sqlW.WriteSprocEpilog();
            }
        }

        protected string GetPKIfStatment()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("if ");
            bool first = true;
            foreach (PublicationPublishedTableArticleColumn col in _PublishedTable.PKColumns)
            {
                if (!first)
                {
                    sb.Append(" or ");
                }
                else
                {
                    first = false;
                }
                sb.AppendFormat("substring(@bitmap,{0},1) & {1} = {1}", ((int)((col.ColumnOrder) / 8) + 1).ToString(), Math.Pow(2, (col.ColumnOrder - 1) % 8).ToString());
            }
            return sb.ToString();
        }

        protected string GetUpdateColumns(bool skipPKs)
        {
            StringBuilder sb = new StringBuilder();
            bool first = true;
            foreach (PublicationPublishedTableArticleColumn col in _PublishedTable.Columns)
            {
                // If skipPKs then do not create script for PK columns
                if (!(skipPKs && col.IsPartOfPK == 1))
                {
                    if (first)
                    {
                        first = false;
                    }
                    else
                    {
                        sb.Append("\r\n,");
                    }
                    sb.AppendFormat("[{0}] = case substring(@bitmap,{1},1) & {2} when {2} then @c{3} else [{0}] end", col.ColumnName, (int)((col.ColumnOrder - 1) / 8) + 1, Math.Pow(2, (col.ColumnOrder - 1) % 8), col.ColumnOrder);
                }
            }
            return sb.ToString();
        }

        protected string GetUpdateWhereClause()
        {
            StringBuilder sb = new StringBuilder();
            int i = 1;
            foreach (PublicationPublishedTableArticleColumn col in _PublishedTable.PKColumns)
            {
                sb.AppendFormat("[{0}] = @pkc{1}", col.ColumnName, i);
                if (i != _PublishedTable.PKColumns.Length)
                {
                    sb.Append(" and ");
                }
                i++;
            }
            return sb.ToString();
        }

    #endregion
    }
}
